sqlserver 下三种批量插入数据的方法

本文将介绍三种批量插入数据的方法。第一种方法是使用循环语句逐个将数据项插入到数据库中；第二种方法使用的是SqlBulkCopy，使您可以用其他源的数据有效批量加载 SQL Server 表；第三种使用的方法是sql server中的表值参数方法，表值参数是 SQL Server 2008 中的新参数类型。表值参数是使用用户定义的表类型来声明的。使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据。

      代码示例：

      此例子为控制台输出程序，有两个类，一个为BulkData类，主要实现了表值参数和sqlbulkcopy是如何插入数据的，一个类为Repository，一个app.config配置文件。所用数据库为sql server 2012。

       建库语句：

--Create Tableuse BulkDBgo

if exists(select * from sys.objects where object_id=OBJECT_ID(N'[dbo].[BulkTable]') and type in(N'U'))drop table [dbo].BulkTableCreate table BulkTable(Id int primary key,UserName nvarchar(32),Pwd varchar(16))go

--Create Table Valueduse BulkDBgo

if exists(select * from sys.types st join sys.schemas ss on st.schema_id=ss.schema_idwhere st.name=N'[BulkType]' and ss.name=N'dbo')drop type [dbo].[BulkType]go

create type [dbo].[BulkType] as table  (   Id int,   UserName nvarchar(32),   Pwd varchar(16)   )go

select * from dbo.BulkTable

BulkData.cs

namespace BulkData{    class BulkData    {        public static void TableValuedToDB(DataTable dt)        {            SqlConnection sqlConn = new SqlConnection(              ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);            const string TSqlStatement =             "insert into BulkTable (Id,UserName,Pwd)" +             " SELECT nc.Id, nc.UserName,nc.Pwd" +             " FROM @NewBulkTestTvp AS nc";            SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);            SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);

            catParam.SqlDbType = SqlDbType.Structured;

            catParam.TypeName = "dbo.BulkType";            try            {                sqlConn.Open();                if (dt != null && dt.Rows.Count != 0)                {                    cmd.ExecuteNonQuery();                }            }            catch (Exception ex)            {                throw ex;            }            finally            {                sqlConn.Close();            }        }

        public static DataTable GetTable()        {            DataTable dt = new DataTable();

            dt.Columns.AddRange(new DataColumn[]{new DataColumn("Id",typeof(int)),new DataColumn("UserName",typeof(string)),new DataColumn("Pwd",typeof(string))});

            return dt;        }

        public static void BulkToDB(DataTable dt)        {            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);            bulkCopy.DestinationTableName = "BulkTable";            bulkCopy.BatchSize = dt.Rows.Count;

            try            {                sqlConn.Open();                if (dt != null && dt.Rows.Count != 0)                    bulkCopy.WriteToServer(dt);            }            catch (Exception ex)            {                throw ex;            }            finally            {                sqlConn.Close();                if (bulkCopy != null)                    bulkCopy.Close();            }        }    }}

Repository.cs

namespace BulkData{    public class Repository    {        public static void UseSqlBulkCopyClass()        {            Stopwatch sw = new Stopwatch();            for (int outLayer = 0; outLayer < 10; outLayer++)            {                DataTable dt = BulkData.GetTable();                for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)                {                    DataRow r = dt.NewRow();                    r[0] = count;                    r[1] = string.Format("User-{0}", count * outLayer);                    r[2] = string.Format("Password-{0}", count * outLayer);                    dt.Rows.Add(r);                }                sw.Start();                BulkData.BulkToDB(dt);                sw.Stop();                Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1));            }

            Console.ReadLine();        }

        public static void UseTableValue()        {            Stopwatch sw = new Stopwatch();

            for (int outLayer = 0; outLayer < 10; outLayer++)            {                DataTable dt = BulkData.GetTable();

                for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)                {                    DataRow dataRow = dt.NewRow();                    dataRow[0] = count;                    dataRow[1] = string.Format("User-{0}", count * outLayer);                    dataRow[2] = string.Format("Password-{0}", count * outLayer);                    dt.Rows.Add(dataRow);                }

                sw.Start();                BulkData.TableValuedToDB(dt);                sw.Stop();

                Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1));            }

            Console.ReadLine();        }

        public static void UserNormalInsert()        {            Stopwatch sw = new Stopwatch();

            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);

            SqlCommand sqlComm = new SqlCommand();            sqlComm.CommandText = string.Format("insert into BulkTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");            sqlComm.Parameters.Add("@p0", SqlDbType.Int);            sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar);            sqlComm.Parameters.Add("@p2", SqlDbType.VarChar);            sqlComm.CommandType = CommandType.Text;            sqlComm.Connection = sqlConn;            sqlConn.Open();

            try            {                for (int outLayer = 0; outLayer < 10; outLayer++)                {                    for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)                    {

                        sqlComm.Parameters["@p0"].Value = count;                        sqlComm.Parameters["@p1"].Value = string.Format("User-{0}", count * outLayer);                        sqlComm.Parameters["@p2"].Value = string.Format("Password-{0}", count * outLayer);                        sw.Start();                        sqlComm.ExecuteNonQuery();                        sw.Stop();                    }

                    Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1));                }            }            catch (Exception ex)            {                throw ex;            }            finally            {                sqlConn.Close();            }

            Console.ReadLine();        }    }}

App.config

 Program.cs

namespace BulkData{    class Program    {                    static void Main(string[] args)        {            //Repository.UseSqlBulkCopyClass();            Repository.UseTableValue();            //Repository.UserNormalInsert();        }            }}

三种方法分别插入100万条数据所用的时间为：

       循环语句所用时间：